import sqlite3
from typing import Optional

DB_NAME = "s_he.db"

# 初始化数据库
def init_db():
    conn = sqlite3.connect(DB_NAME)
    cur = conn.cursor()

    # 用户表
    cur.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL,
        role TEXT CHECK(role IN ('user', 'admin')) NOT NULL
    )
    ''')

    # 参数表
    cur.execute('''
    CREATE TABLE IF NOT EXISTS user_params (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER UNIQUE NOT NULL,
        param1 TEXT DEFAULT '',
        param2 TEXT DEFAULT '',
        param3 TEXT DEFAULT '',
        FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
    )
    ''')

    conn.commit()
    conn.close()

# 注册
def register(username: str, password: str, role: str = 'user') -> bool:
    conn = sqlite3.connect(DB_NAME)
    cur = conn.cursor()
    try:
        cur.execute("INSERT INTO users (username, password, role) VALUES (?, ?, ?)", (username, password, role))
        user_id = cur.lastrowid
        cur.execute("INSERT INTO user_params (user_id) VALUES (?)", (user_id,))
        conn.commit()
        return True
    except sqlite3.IntegrityError:
        return False
    finally:
        conn.close()

# 登录
def login(username: str, password: str) -> Optional[dict]:
    conn = sqlite3.connect(DB_NAME)
    cur = conn.cursor()
    cur.execute("SELECT id, role FROM users WHERE username = ? AND password = ?", (username, password))
    result = cur.fetchone()
    conn.close()
    if result:
        # print(f"User {username} logged in successfully.")
        return {"id": result[0], "username": username, "role": result[1]}
    return None

# 删除用户（管理员操作）
def delete_user(admin_id: int, target_username: str) -> bool:
    conn = sqlite3.connect(DB_NAME)
    cur = conn.cursor()

    # 确保发起者是管理员
    cur.execute("SELECT role FROM users WHERE id = ?", (admin_id,))
    if cur.fetchone()[0] != "admin":
        return False

    cur.execute("DELETE FROM users WHERE username = ?", (target_username,))
    conn.commit()
    deleted = cur.rowcount > 0
    conn.close()
    return deleted

# 获取参数（用户或管理员）
def get_params(user_id: int) -> Optional[dict]:
    conn = sqlite3.connect(DB_NAME)
    cur = conn.cursor()
    cur.execute("SELECT param1, param2, param3 FROM user_params WHERE user_id = ?", (user_id,))
    result = cur.fetchone()
    conn.close()
    if result:
        return {"param1": result[0], "param2": result[1], "param3": result[2]}
    return None

# 修改参数（只能修改，不能增删）
def update_params(user_id: int, param_updates: dict) -> bool:
    allowed_keys = {'param1', 'param2', 'param3'}
    updates = {k: v for k, v in param_updates.items() if k in allowed_keys}
    if not updates:
        return False

    conn = sqlite3.connect(DB_NAME)
    cur = conn.cursor()
    sets = ', '.join([f"{k} = ?" for k in updates.keys()])
    values = list(updates.values()) + [user_id]
    cur.execute(f"UPDATE user_params SET {sets} WHERE user_id = ?", values)
    conn.commit()
    updated = cur.rowcount > 0
    conn.close()
    return updated

# 修改密码
def update_password(user_id: int, new_password: str) -> bool:
    conn = sqlite3.connect(DB_NAME)
    cur = conn.cursor()
    cur.execute("UPDATE users SET password = ? WHERE id = ?", (new_password, user_id))
    conn.commit()
    updated = cur.rowcount > 0
    conn.close()
    return updated

# 查询用户信息（管理员）
def list_users(admin_id: int=0) -> list:
    conn = sqlite3.connect(DB_NAME)
    cur = conn.cursor()
    # cur.execute("SELECT role FROM users WHERE id = ?", (admin_id,))
    # if cur.fetchone()[0] != 'admin':
    #     conn.close()
    #     return []

    cur.execute("SELECT id, username, role FROM users")
    results = cur.fetchall()
    conn.close()
    return [{"id": row[0], "username": row[1], "role": row[2]} for row in results]


